﻿namespace dogd
{
	using System;
	using System.Collections.Generic;
	using System.Linq;
	using System.Text;

	/// <summary>
	/// SqlStatementGenerator is used to generate Sql statements based on supplied TableMetadata 
	/// </summary>
	public class SqlStatementGenerator : ISqlGenerator
	{
		/// <summary>
		/// Construct generator using TableMetadata
		/// </summary>
		/// <param name="model"></param>
		public SqlStatementGenerator(TableMetadata model)
		{
			this.Metadata = model;
		}

		private TableMetadata Metadata { get; set; }

		/// <summary>
		/// Generates sql for the given crud option.
		/// </summary>
		/// <param name="crudOption">The crud operation.</param>
		/// <returns>The sql text.</returns>
		public string GenerateFor(CrudOption crudOption)
		{
			return this.GenerateFor(crudOption, null);
		}

		/// <summary>
		/// Generates sql for the given crud option.
		/// </summary>
		/// <param name="crudOption">The crud operation.</param>
		/// <param name="filters">Optional filters.</param>
		/// <returns>The sql text.</returns>
		public string GenerateFor(CrudOption crudOption, FilterByCollection filters)
		{
			string sql = string.Empty;

			switch (crudOption)
			{
				case CrudOption.Insert:

					sql = this.Insert();
					break;

				case CrudOption.Select:

					if (filters == null)
					{
						sql = this.SelectAll();
					}
					else
					{
						sql = this.Select(filters);
					}

					break;

				case CrudOption.SelectById:

					sql = this.Select();
					break;

				case CrudOption.SelectByPrimaryKey:

					sql = this.Select(SelectOption.ByPrimaryKey);
					break;

				case CrudOption.SelectByForeignKey:

					sql = this.Select(SelectOption.ByForeignKey);
					break;

				case CrudOption.Update:

					sql = this.Update();
					break;

				case CrudOption.Delete:

					sql = this.Delete(DeleteOption.ByPrimaryKey);
					break;

				case CrudOption.DeleteByForeignKey:

					sql = this.Delete(DeleteOption.ByForeignKey);
					break;

				default:
					throw new ArgumentException("Unexpected value", "option");
			}

			return sql;
		}

		/// <summary>
		/// Return all items in a table with no conditions
		/// </summary>
		/// <returns></returns>
		public string SelectAll()
		{
			return Select(this.Metadata, null, null);
		}

		/// <summary>
		/// Return all items in a table with an order
		/// </summary>
		/// <param name="order"></param>
		/// <returns></returns>
		public string SelectAll(OrderBy order)
		{
			return Select(this.Metadata, null, order);
		}

		/// <summary>
		/// Return items based on lookup filters defined by metadata
		/// </summary>
		/// <returns></returns>
		public string Select()
		{
			FilterByCollection filters = this.Metadata.LookupFilters;

			if (filters.Count == 0)
			{
				throw new NotSupportedException("No Lookups set for this type");
			}

			return Select(this.Metadata, filters, null);
		}

		/// <summary>
		/// Return all items filters by one of the SelectOption values 
		/// </summary>
		/// <param name="option"></param>
		/// <returns></returns>
		public string Select(SelectOption option)
		{
			FilterByCollection filters;
			
			if (option == SelectOption.ByPrimaryKey)
			{
				filters = this.Metadata.PrimaryKeyFilters;
			}
			else
			{
				filters = this.Metadata.ForeignKeyFilters;
			}

			if (filters.Count == 0)
			{
				throw new NotSupportedException("No Lookups set for this type");
			}

			return Select(this.Metadata, filters, null);
		}

		/// <summary>
		/// Return all items passing filtered criteria
		/// </summary>
		/// <param name="filters"></param>
		/// <returns></returns>
		public string Select(FilterByCollection filters)
		{
			return Select(this.Metadata, filters, null);
		}

		private static string Select(TableMetadata table, FilterByCollection filters, OrderBy order)
		{
			var columnNames = table.ColumnNames;

			StringBuilder builder = new StringBuilder();
			builder.Append("SELECT ");
			builder.Append(string.Join(", ", columnNames.ToArray()));

			builder.AppendFormat(" FROM {0}", table.Name);

			builder.Append(Where(filters));

			if (order != null)
			{
				builder.Append(OrderBy(order));
			}

			return builder.ToString();
		}

		private static string Where(FilterByCollection filters)
		{
			StringBuilder builder = new StringBuilder();

			if (filters != null && filters.Count > 0)
			{
				builder.Append(" WHERE ");

				var filterParams = new List<string>();

				foreach (FilterBy filter in filters)
				{
					filterParams.Add(filter.ToString());
				}

				builder.Append(string.Join(" AND ", filterParams.ToArray()));
			}

			return builder.ToString();
		}

		/// <summary>
		/// Insert into a table based on all insertable column names and parameters. Returns last inserted id.
		/// </summary>
		/// <returns></returns>
		public string Insert()
		{
			var columnNames = this.Metadata.InsertableColumnNames;
			var parameters = this.Metadata.InsertableParameters;

			StringBuilder builder = new StringBuilder();

			builder.AppendFormat("INSERT INTO {0} ( ", this.Metadata.Name);
			builder.Append(string.Join(", ", columnNames.ToArray()));
			builder.Append(" ) VALUES ( ");
			builder.Append(string.Join(", ", parameters.ToArray()));
			builder.Append(" )");
			builder.Append("; SELECT last_insert_rowid() ");

			return builder.ToString();
		}
		
		/// <summary>
		/// Update a row based on the primary key
		/// </summary>
		/// <returns></returns>
		public string Update()
		{
			FilterByCollection filters = this.Metadata.PrimaryKeyFilters;

			if (filters.Count == 0)
			{
				throw new NotSupportedException("No Lookups set for this type");
			}

			return Update(this.Metadata, filters);
		}

		/// <summary>
		/// Update a row based on the filter
		/// </summary>
		/// <param name="filters"></param>
		/// <returns></returns>
		public string Update(FilterByCollection filters)
		{
			return Update(this.Metadata, filters);
		}

		/// <summary>
		/// Update a row based on metadata and filter
		/// </summary>
		/// <param name="table"></param>
		/// <param name="filters"></param>
		/// <returns></returns>
		public static string Update(TableMetadata table, FilterByCollection filters)
		{
			var parameters = table.FormattedParameterStrings;

			StringBuilder builder = new StringBuilder();

			builder.AppendFormat("UPDATE {0} SET ", table.Name);
			builder.Append(string.Join(", ", parameters.ToArray()));

			builder.Append(Where(filters));

			return builder.ToString();
		}

		/// <summary>
		/// Delete based on primary key
		/// </summary>
		/// <returns></returns>
		public string Delete()
		{
			return Delete(this.Metadata, this.Metadata.PrimaryKeyFilters);
		}

		/// <summary>
		/// Delete based on DeleteOption value
		/// </summary>
		/// <param name="option"></param>
		/// <returns></returns>
		public string Delete(DeleteOption option)
		{
			FilterByCollection filters;

			if (option == DeleteOption.ByPrimaryKey)
			{
				filters = this.Metadata.PrimaryKeyFilters;
			}
			else
			{
				filters = this.Metadata.ForeignKeyFilters;
			}

			return Delete(this.Metadata, filters);
		}

		/// <summary>
		/// Delete based on filter
		/// </summary>
		/// <param name="filters"></param>
		/// <returns></returns>
		public string Delete(FilterByCollection filters)
		{
			return Delete(this.Metadata, filters);
		}

		private static string Delete(TableMetadata table, FilterByCollection filters)
		{
			var builder = new StringBuilder();

			builder.AppendFormat("DELETE FROM {0} ", table.Name);

			if (filters.Count == 0)
			{
				throw new NotSupportedException("No Lookups set for this type");
			}

			builder.Append(Where(filters));

			return builder.ToString();
		}

		/// <summary>
		/// Order fragement based on OrderBy value
		/// </summary>
		/// <param name="order"></param>
		/// <returns></returns>
		public static string OrderBy(OrderBy order)
		{
			var builder = new StringBuilder();

			builder.AppendFormat(" {0}", order.ToString());

			return builder.ToString();
		}

		/// <summary>
		/// Count fragment based on first lookup filter
		/// </summary>
		/// <returns></returns>
		public string Count()
		{
			return Count(this.Metadata, this.Metadata.LookupFilters[0], null);
		}

		/// <summary>
		/// Count fragment based on filter column name and filter collection
		/// </summary>
		/// <param name="countField"></param>
		/// <param name="filters"></param>
		/// <returns></returns>
		public string Count(FilterBy countField, FilterByCollection filters)
		{
			return Count(this.Metadata, countField, filters);
		}

		private static string Count(TableMetadata table, FilterBy countField, FilterByCollection filters)
		{
			var builder = new StringBuilder();

			builder.Append("SELECT ");

			if (countField != null)
			{
				builder.AppendFormat("COUNT({0}) ", countField.Name);
			}
			else
			{
				builder.Append("COUNT(*) ");
			}

			builder.AppendFormat("FROM {0} ", table.Name);
			builder.Append(Where(filters));

			return builder.ToString();
		}
	}
}
